/*
 * DatabaseDemoHostApplication.java
 *
 * Copyright (c) 2008 Sun Microsystems, Inc.
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to
 * deal in the Software without restriction, including without limitation the
 * rights to use, copy, modify, merge, publish, distribute, sublicense, and/or
 * sell copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
 * DEALINGS IN THE SOFTWARE.
 */
package org.sunspotworld.demo;


import com.sun.spot.io.j2me.radiogram.*;
import com.sun.spot.peripheral.TimeoutException;
import java.io.*;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.StringTokenizer;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Pattern;
import javax.microedition.io.*;

import org.apache.commons.httpclient.HttpClient;
import org.apache.commons.httpclient.HttpMethod;
import org.apache.commons.httpclient.NameValuePair;
import org.apache.commons.httpclient.methods.GetMethod;

/**
 * This application is the 'on Desktop' portion of the DatabaseDemo.
 * This host application collects sensor samples sent by the 'on SPOT'
 * portion running on neighboring SPOTs and stores the samples in a
 * database. The application then performs some simple analysis on the
 * collected data using SQL queries.
 *
 * This demo requires the popular open-source database, mySQL, to be
 * installed. See http://www.sun.com/mysql for downloading and installation
 * instructions. This application was tested with mySQL version
 * 5.0.51 and the JDBC driver for that is stored in the jdbc-driver
 * directory. If you end up using a different version, be sure to edit the
 * build.properties file to include the appropriate JDBC driver in the
 * user.classpath property.
 *
 * @author: Vipul Gupta
 */
public class DatabaseDemoHostApplication {
    // Broadcast port on which we listen for sensor samples
    private static final int DATA_SINK_PORT = 67;
    // This demo assumes a mySQL database has been installed on the
    // local host at the default port (3306), has a database called
    // Test and can be manipulated using the default administrator
    // settings (empty password for user 'root'). The JDBC driver
    // needed to communicate with this database is specified in JDBC_DRIVER.
    // If your setup is based on a different database and driver, you'll
    // need to modify these settings appropriately.
    // Obviously, an empty password should never be used for anything more
    // serious than a test setup.
    private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/";
    private static final String DATABASE_NAME = "Test";
    private static final String DATABASE_USER = "root";
    private static final String DATABASE_PASSWORD = "";
    private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    // Name of the database table where we store sensor readings
    private static final String DATA_TABLE_NAME = "a3r0";
    private static final String POS_ID = "a3r0";
    private static final String X = "200";
    private static final String Y = "400";
    //private static final String PROGRAMROLE = "REFERENCEPOINTS";
    private static final String PROGRAMROLE="SAMPLER";
    // Duration (in milliseconds) for which to collect samples.
    private static final int SAMPLING_DURATION = 1000;
    // This is how long we block waiting to read data on the
    // radio connection. Without a timeout, the host application
    // could run well beyond the duration specified above, e.g. if
    // there are no SPOTs transmitting sensor samples.
    private static final int CONNECTION_TIMEOUT = 10000;
    private Statement stmt = null;
    private java.sql.Connection dbCon = null;
    private RadiogramConnection rCon = null;

    /**
     * getConnection
     *
     * @throws IOException
     */
    private void getHttpConnection(String packet) throws IOException {
        String spotID = "2051";
        NameValuePair nvp1 = new NameValuePair("command", "sendpacket");
        NameValuePair nvp2 = new NameValuePair("packet", packet);
        String url = "http://localhost:8080";

        //Instantiate an HttpClient
        HttpClient client = new HttpClient();
        //Instantiate a GET HTTP method
        HttpMethod method = new GetMethod(url);
        //Define name-value pairs to set into the QueryString
        method.setQueryString(new NameValuePair[]{nvp1, nvp2});
        try {
            int statusCode = client.executeMethod(method);
            if (statusCode == 200) {
                String responseString = method.getResponseBodyAsString();
                HashMap<String, String> response = new HashMap<String, String>(11);
                parseGetResponse(responseString, response);
                method.releaseConnection();
            }
        } catch (IOException e) {
            throw e;
        }

    }

    private void parseGetResponse(String responseString, HashMap<String, String> response) {
        Pattern p = Pattern.compile("[\\{\\}\\=\\, ]++");
        String[] split = p.split(responseString);
        for (int i = 1; i < split.length; i += 2) {
            response.put(split[i], split[i + 1]);
        }
    }

    private void run() throws Exception {
        try {
            setUp();
            collectData();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            tearDown();
        }
    }

    private void setUp() throws Exception {
        System.out.println("Database demo application starting ... ");
        try {
            // Register the JDBC driver for mysql
            // This is typically found in a JAR file
            // named something like
            // mysql-connector-java-<version>-bin.jar
            // which should be in the classpath
            // See user.classpath in build.properties
            // for this Sun SPOT host application
            Class.forName(JDBC_DRIVER);

            // Define URL of database server for
            String url = DATABASE_URL + DATABASE_NAME;

            // Get a connection to the database for given user/password
            dbCon = DriverManager.getConnection(url, DATABASE_USER,
                    DATABASE_PASSWORD);

            // Display URL and connection information
            System.out.println("URL: " + url);
            System.out.println("Connection: " + dbCon);
        } catch (Exception e) {
            System.err.println("setUp caught " + e);
            System.err.println("Make sure that mySQL is installed properly \n"
                    + "and has a Test database accessible via the \n"
                    + "default administrator settings on localhost:3306\n");
            throw e;
        }

        try {
            // Get a Statement object
            stmt = dbCon.createStatement();

            // Delete the data collection table if left over
            // from a previous run. If no table exists, an
            // exception will be thrown.
            if (PROGRAMROLE.equals("REFERENCEPOINTS")) {
                try {
                    stmt.executeUpdate("DROP TABLE " + DATA_TABLE_NAME);
                    System.out.println("Existing table deleted.");
                } catch (Exception e) {
                    System.out.println(e.getMessage());
                    System.out.println(
                            "No existing table to delete.");
                }

                // Create a table with three columns:
                // spotId    padded to 20 chars identifier for SPOT
                // timestamp YYYY-MM-DD hh:mm:ss string when reading was taken
                // reading   light sensor reading (4 is simply the display
                //           width but an unsigned smallint can store any value
                //           from 0 to 65535)
                //       stmt.executeUpdate(
                //               "CREATE TABLE " + DATA_TABLE_NAME + " (spotId char(20), " +
                //               "timestamp timestamp, reading smallint(4) unsigned)");
                stmt.executeUpdate(
                        "CREATE TABLE " + DATA_TABLE_NAME + " (spotId char(30), "
                        + "SeqNr char(20),RSSI char(20), LQI char(20), Time varchar(40), Sendingtime varchar(40),X char(20),Y char(20),PosId char(20))");

            }

            // Let's also open up a server-side broadcast radiogram connection
            // on which to listen for sensor readings being sent by different
            // SPOTs
            rCon = (RadiogramConnection) Connector.open("radiogram://:"
                    + DATA_SINK_PORT);
            // wait a finite time for data to arrive whenever we call receive()
            rCon.setTimeout(CONNECTION_TIMEOUT);
            System.out.println("setUp completed successfully");
        } catch (Exception e) {
            System.err.println("setUp caught " + e.getMessage());
            throw e;
        }
    }

    public void collectData() {
        String spotID = null;
        String packetData = null;
        String Time;

        String ts = null;
        int val = 0;
        int sampleCnt = 0;
        Datagram dg = null;
        long start = 0L;

        System.out.println("Sampling for " + (SAMPLING_DURATION / 1000)
                + " seconds ...\n[Each * indicates one sample, ! implies"
                + " radio timed out waiting for a sample]");
        try {
            dg = rCon.newDatagram(rCon.getMaximumLength());
        } catch (IOException ex) {
            Logger.getLogger(DatabaseDemoHostApplication.class.getName()).log(Level.SEVERE, null, ex);
        }


        do
        {
           //Utils.sleep(1000);
           String httpPacket="";
           sampleCnt=0;

            start = System.currentTimeMillis();
        // Main data collection loop
        while ((System.currentTimeMillis() - start) < SAMPLING_DURATION) {
            try {
                // Read sensor sample received over the radio
                rCon.receive(dg);
                spotID = dg.getAddress();
                packetData = dg.readUTF();  // read sender's Id
                Time = "0";

                val = 2;

                if (packetData.charAt(0) == 'P') {
                    if (PROGRAMROLE.equals("REFERENCEPOINTS")) {
                        String[] address = new String[20];
                        String[] seqNr = new String[20];
                        String[] sigStrength = new String[20];
                        String[] sigQuality = new String[20];
                        String[] timestamps = new String[20];
                        int j = 0;

                        StringTokenizer tokenizer = new StringTokenizer(packetData.substring(0, packetData.indexOf("#")), ";");

                        String temp = null;
                        String receivedTime = packetData.substring(packetData.indexOf("#") + 1, packetData.length());
                        while (tokenizer.hasMoreTokens()) {
                            temp = tokenizer.nextToken();
                            address[j] = temp.substring(0, temp.indexOf("%"));
                            seqNr[j] = temp.substring(temp.indexOf("%") + 1, temp.indexOf('|'));
                            sigStrength[j] = temp.substring(temp.indexOf("|") + 1, temp.indexOf('$'));
                            sigQuality[j] = temp.substring(temp.indexOf("$") + 1, temp.length());
                            SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS");
                            Date d = new Date();
                            timestamps[j] = s.format(d).toString();
                            j++;
                        }

                        for (int i = 0; i < j; i++) {
                            System.out.println("INSERT INTO " + DATA_TABLE_NAME
                                    + "(spotId,seqNr,RSSI,LQI,Time,SendingTime,X,Y,PosId)"
                                    + " VALUES(\'" + address[i] + "\',\'" + seqNr[i] + "\',\'" + sigStrength[i] + "\',\'" + sigQuality[i] + "\',\'" + timestamps[i] + "\',\'" + receivedTime + "\',\'" + X + "\',\'" + Y + "\',\'" + POS_ID + "\')");
                            stmt.executeUpdate("INSERT INTO " + DATA_TABLE_NAME
                                    + "(spotId,seqNr,RSSI,LQI,Time,SendingTime,X,Y,PosId)"
                                    + " VALUES(\'" + address[i] + "\',\'" + seqNr[i] + "\',\'" + sigStrength[i] + "\',\'" + sigQuality[i] + "\',\'" + timestamps[i] + "\',\'" + receivedTime + "\',\'" + X + "\',\'" + Y + "\',\'" + POS_ID + "\')");

                        }

                    } else if (PROGRAMROLE.equals("SAMPLER")) {
                        httpPacket+=packetData.substring(0)+"*";
                    }


                }
                sampleCnt++;
            } catch (TimeoutException e) {
                System.err.println("!");
            }  catch (Exception e) {
                System.err.println("Caught " + e
                        + " while reading sensor samples.");
                System.out.println("fehler");
            }
        }

        System.out.println("\nDone! Collected " + sampleCnt + " samples.");
        if(PROGRAMROLE.equals("SAMPLER")){
            System.out.println("httppacket= "+httpPacket);
                try {
                    getHttpConnection(httpPacket);
                } catch (IOException ex) {
                    System.out.println("httppacket=0");
                    httpPacket="";
                    Logger.getLogger(DatabaseDemoHostApplication.class.getName()).log(Level.SEVERE, null, ex);
                }
        }

        }while(PROGRAMROLE.equals("SAMPLER"));
    }

   
    public void tearDown() throws Exception {
        if (dbCon != null) {
            dbCon.close();
        }
        if (rCon != null) {
            rCon.close();
        }
        System.exit(0);
    }

    /**
     * Start up the host application.
     *
     * @param args any command line arguments
     */
    public static void main(String[] args) throws Exception {
        DatabaseDemoHostApplication app = new DatabaseDemoHostApplication();
        app.run();
    }
}
